﻿use master
create database hrm_adcc
go
use hrm_adcc
go
----------------------------------------------------
--created_date: 13/08/2012
--desc: tbl_users
create table tbl_users
(
	user_id int identity(1,1) primary key,
	email varchar(150),
	username varchar(50),
	password varchar(150),
	user_status tinyint,
	created_date datetime,
	active_date datetime,
	activation_code varchar(10),
	salt varchar(32)
)
go
----------------------------------------------------
--created_date:13/08/2012
--desc:table user information
create table tbl_user_infor
(
	user_infor_id int identity(1,1) primary key,
	user_id int references tbl_users(user_id),
	infor_key nvarchar(30),
	infor_value nvarchar(500)
)
go
----------------------------------------------------
--created_date:13/08/2012
--desc:group user
--eg: Project Mgr,Consultant,Administrator,...
create table tbl_user_group
(
	group_id int identity(1,1) primary key,
	group_name nvarchar(30),
	group_desc nvarchar(150),
	group_enable bit,
	group_parent_id int references tbl_user_group(group_id) 	
)
go
-----------------------------------------------------
--created_date:13/08/2012
--desc:action of user
--eg: Editor, reviewer,delete,...
create table tbl_ref_role_action
(
	role_action_id int identity(1,1) primary key,
	role_action_name nvarchar(30),
	role_action_desc nvarchar(150)
)
go
---------------------------------------------------
--created_date:13/08/2012
--desc:references role_action , group , user_id
create table tbl_user_ref_roles
(
	user_role_id int identity(1,1) primary key,
	group_id int references tbl_user_group(group_id),
	--role_action_id int references tbl_ref_role_action(role_action_id),		//cách 1
	user_id int references tbl_users(user_id),
	date_from datetime,
	date_to datetime
)
go
--------------------------------------------------
--created_date:13/08/2012
--desc:table page in system, page_status = 1 is enable, page_status=0 is disable, page_type=1 is admin page
create table tbl_page
(
	page_id int identity(1,1) primary key,
	page_name nvarchar(100),
	page_desc nvarchar(150),
	page_path varchar(150),
	page_status tinyint,
	page_type tinyint,
	page_order int,
	page_parent int references tbl_page(page_id)
)
go
--------------------------------------------------
--created_date:13/08/2012
--desc: assign role page
create table tbl_page_role
(
	page_role_id int identity(1,1) primary key,
	page_id int references tbl_page(page_id),
	group_id int references tbl_user_group(group_id),
	role_action_id int references tbl_ref_role_action(role_action_id),    --cách 2
	created_date datetime
)
go
---------------------------------------------------
--create table tbl_type_employee						--bảng loại nhân viên
--(
--	type_employee_id int identity(1,1) primary key,
--	type_employee_name nvarchar(150),
--	type_desc ntext,
--	parent_type int references tbl_type_employee(type_employee_id)
--)
--go
---------------------------------------------------
create table tbl_object_employee					--bảng đối tượng nhân viên
(
	object_employee_id int identity(1,1) primary key,
	object_employee_name nvarchar(150),
	object_desc ntext,
	parent_object int references tbl_object_employee(object_employee_id)
)
go
-----------------------------------------------
--create table tbl_branch							--bảng cơ sở
--(
--	branch_id int identity(1,1) primary key,
--	branch_name nvarchar(100),
--	branch_description nvarchar(500),
--	branch_address nvarchar(150)
--)
--go
---------------------------------------------
create table tbl_departments					-- bảng phòng ban
(
	departments_id int identity(1,1) primary key,
	departments_name nvarchar(150),
	descriptions ntext,
	--branch_id int references tbl_branch(branch_id),
	departments_status tinyint,
	parent_departments int references tbl_departments(departments_id)
)
go
----------------------------------------------
create table tbl_type_leave						--Loại hình nghỉ
(
	type_leave_id int identity(1,1) primary key,
	type_leave_name nvarchar(150),
	descriptions ntext
)
go
----------------------------------------------
create table tbl_type_contract					--Loại hợp đồng
(
	type_contract_id int identity(1,1) primary key,
	type_contract_name nvarchar(200),
	descriptions ntext
)
go

------------------------------------------------------------
--create table tbl_type_school								--bảng loại trường đào tạo
--(
--	type_school_id int identity(1,1) primary key,
--	type_names nvarchar(150),
--	descriptions ntext
--)
--go
-----------------------------------------------------------
create table tbl_school										--bảng trường đào tạo
(
	school_id int identity(1,1) primary key,
	--type_school_id int references tbl_type_school(type_school_id),
	school_name nvarchar(150),
	school_address nvarchar(150),
	descriptions ntext
)
go
---------------------------------------------------
create table tbl_type_reward					--bảng danh mục khen thưởng
(
	type_reward_id int identity(1,1) primary key,
	type_reward_name nvarchar(150),
	type_reward_desc ntext,
	parent_id int references tbl_type_reward(type_reward_id)
)
go
---------------------------------------------------
--created_date:13/08/2012
--desc: table employee
create table tbl_employee
(
	employee_id int identity(1,1) primary key,
	employee_code varchar(50),
	full_name nvarchar(150),
	alias nvarchar(150),						--bí danh(new)
	
	b_day int,
	b_month int,
	b_year int,
	birth_day datetime,
	
	phone varchar(50),
	mobile varchar(50),							--mobile(new)
	bank_account nvarchar(50),					--tài khoản ngân hàng(new)
	bank_name nvarchar(150),					--ngân hàng mở tài khoản(new)
	tax_code nvarchar(50),						--mã số thuế(new)
	family_reduction nvarchar(150),				--hồ sơ giảm trừ gia cảnh(new)	--file
	house_land_status nvarchar(1000),			--Tình hình nhà đất(new)
	email varchar(100),
	gender bit,
	ethnic nvarchar(100),						--dân tộc(new)
	religion nvarchar(100),						--tôn giáo(new)
	hometown nvarchar(500),
	current_address nvarchar(500),				--dia chi hien tai
	household_registration nvarchar(500),		--Hộ khẩu thường trú
	
	dateIn_doan_d nvarchar(10),						--ngày vào đoàn (new 26/03/2013)
	dateIn_doan_m nvarchar(10),						--tháng vào đoàn (new 26/03/2013)
	dateIn_doan_y nvarchar(10),						--năm vào đoàn (new 26/03/2013)
	dateIn_doan datetime,						--ngày vào đoàn
	
	
	dateIn_dang_d nvarchar(10),						--ngày vào đảng(new 26/03/2013)
	dateIn_dang_m nvarchar(10),						--tháng vào đảng (new 26/03/2013)
	dateIn_dang_y nvarchar(10),						--năm vào đảng (new 26/03/2013)
	dateIn_dang datetime,						--ngày vào đảng

	ranks nvarchar(50),							--cấp bậc
	salary_coefficient	nvarchar(50),			--hệ số lương
	positions	nvarchar(100),					--chức vụ
	unit nvarchar(200),						--Đoàn thể
	
	date_join_d nvarchar(10),							--Ngày vào công ty (new 26/03/2013)
	date_join_m nvarchar(10),							--Ngày vào công ty (new 26/03/2013)
	date_join_y nvarchar(10),							--Ngày vào công ty (new 26/03/2013)
	date_join datetime,							--Ngày vào công ty
	
	marital_status	nvarchar(50),				--tình trạng hôn nhân
	serial_number_officer nvarchar(50),			--số hiệu sỹ quan
	serial_number_military nvarchar(50),		--số hiệu quân nhân
	identity_card varchar(30),					--CMTND
	
	issuing_date_d nvarchar(10),                --Ngày cấp cmtnd (news-26/3/2013)
	issuing_date_m nvarchar(10),                --Tháng cấp cmtnd (news-26/3/2013)
	issuing_date_y nvarchar(10),                --Năm cấp cmtnd (news-26/3/2013)
	issuing_date datetime,						--ngày cấp CMTND
	
	place_of_issue nvarchar(500),				--nơi cấp CMTND
	army_identity_card varchar(30),				--CMQD
	army_issuing_date_d nvarchar(10),            -- new 26/03/2013
	army_issuing_date_m nvarchar(10),            -- new 26/03/2013
	army_issuing_date_y nvarchar(10),            -- new 26/03/2013
	army_issuing_date datetime,					--ngày cấp
	army_place_of_issue nvarchar(500),			--nơi cấp  CMQD
	cultural_level	nvarchar(50),				--trình độ văn hóa,
	foreign_language nvarchar(50),				--trình độ ngoại ngữ
	
	army_join_d nvarchar(10),							--Ngày nhập ngũ(new 26/03/2013)
	army_join_m nvarchar(10),							--Tháng nhập ngũ(new 26/03/2013)
	army_join_y nvarchar(10),							--Năm nhập ngũ(new 26/03/2013)
	army_join datetime,							--Ngày nhập ngũ(new)


	employee_status tinyint,
	object_employee_id int references tbl_object_employee(object_employee_id),
	avarta nvarchar(100),
	created_date datetime,
	created_by int,
	modified_date datetime,
	modified_by int,
	description_detail text                     -- Mo ta chi tiet hoac them ghi chu cho nhan vien nay

)
go
---------------------------------------------------
create table tbl_employee_family				--bảng thân nhân
(
	family_id int identity(1,1) primary key,
	employee_id int references tbl_employee(employee_id),
	files nvarchar(500),
	fullname nvarchar(150),					--Họ tên (mới)
	hometown nvarchar(250),					--Quê quán (mới)
	current_address nvarchar(350),			--Chỗ ở hiện tại(mới)
	job nvarchar(50),						--Nghề nghiệp(mới)
	birth_day nvarchar(5),						--Năm sinh(mới)
	obj_family tinyint,						--//(1:Vợ,2:Bố,3:Mẹ,4:Chồng,5:Bố chồng,6:Mẹ chồng,7:Bố vợ,8:Mẹ vợ,9:Con ruột,10:Anh chị em ruột)
	family_status tinyint,
	created_date datetime,
	created_by int,
	modified_date datetime,
	modified_by int
)
go
--------------------------------------------------------
create table tbl_department_employee		--bảng quản lý nhân viên thuộc tổ chức hay phòng ban nào
(
	department_employee_id int identity(1,1) primary key,
	departments_id int references tbl_departments(departments_id),
	employee_id int references tbl_employee(employee_id)
)
go
-----------------------------------------------
create table tbl_leave_manage					--quản lý nghỉ
(
	leave_id int identity(1,1) primary key,
	type_leave_id int references tbl_type_leave(type_leave_id),
	employee_id int references tbl_employee(employee_id),
	start_leave datetime,
	start_leave_d nvarchar(10),          --new 26/03/2013
	start_leave_m nvarchar(10),          --new 26/03/2013     
	start_leave_y nvarchar(10),          --new 26/03/2013
	
	end_leave datetime,
	end_leave_d nvarchar(10),            --new 26/03/2013
	end_leave_m nvarchar(10),            --new 26/03/2013
	end_leave_y nvarchar(10),            --new 26/03/2013

	
	descriptions ntext
)
go

----------------------------------------------
create table tbl_contract						--bảng hợp đồng
(
	contract_id int identity(1,1) primary key,
	type_contract_id int references tbl_type_contract(type_contract_id),
	employee_id int references tbl_employee(employee_id),
	contract_name nvarchar(200),
	enable_start datetime,					--ngày có hiệu lực
	enable_start_d nvarchar(10),					--ngày có hiệu lực (new 26/03/2013)
	enable_start_m nvarchar(10),					--ngày có hiệu lực (new 26/03/2013)
	enable_start_y nvarchar(10),					--ngày có hiệu lực (new 26/03/2013)
	
	enable_end datetime,					--ngày hết hiệu lực
	enable_end_d nvarchar(10),					--ngày có hết lực (new 26/03/2013)
	enable_end_m nvarchar(10),					--ngày có hết lực (new 26/03/2013)
	enable_end_y nvarchar(10),					--ngày có hết lực (new 26/03/2013)

	signature_date datetime,				--ngày ký
	signature_date_d nvarchar(10),				--ngày ký (new 26/03/2013)
	signature_date_m nvarchar(10),				--ngày ký (new 26/03/2013)
	signature_date_y nvarchar(10),				--ngày ký (new 26/03/2013)


	signature_name nvarchar(150),			--người ký
	positions nvarchar(150),				--chức danh
	files nvarchar(500),
	contract_status tinyint, 
)
go
-----------------------------------------------
create table tbl_employee_visa_passport			--bảng employee visa_passport
(
	passport_id int identity(1,1) primary key,
	employee_id int references tbl_employee(employee_id),
	passport_code varchar(50),
	passport_number varchar(50),
	passport_issuing_date datetime,						--ngày cấp visa/passport
	passport_issuing_date_d nvarchar(10),						--ngày cấp visa/passport (new 26/03/2013)
	passport_issuing_date_m nvarchar(10),						--ngày cấp visa/passport (new 26/03/2013)
	passport_issuing_date_y nvarchar(10),						--ngày cấp visa/passport (new 26/03/2013)

	enable_start datetime,								--ngày có hiệu lực
	enable_start_d nvarchar(10),								--ngày có hiệu lực (new 26/03/2013)
	enable_start_m nvarchar(10),								--ngày có hiệu lực (new 26/03/2013)
	enable_start_y nvarchar(10),								--ngày có hiệu lực (new 26/03/2013)

	enable_end datetime,								--ngày hết hiệu lực
	enable_end_d nvarchar(10),								--ngày hết hiệu lực (new 26/03/2013)
	enable_end_m nvarchar(10),								--ngày hết hiệu lực (new 26/03/2013)
	enable_end_y nvarchar(10),								--ngày hết hiệu lực (new 26/03/2013)
	place_of_issue nvarchar(500),						--nơi cấp visa/passport
	visa_passport_status tinyint,
	created_date datetime,
	created_by int,
	modified_date datetime,
	modified_by int 
)
go
--------------------------------------------------
create table tbl_employee_job_severance			--bảng nhân viên thôi việc
(
	job_severance_id int identity(1,1) primary key,
	employee_id int references tbl_employee(employee_id),
	form_job_severance nvarchar(500),					--Hình thức thôi việc
	date_job_severance datetime,						--ngày thôi việc
	
	date_job_severance_d nvarchar(10),						--ngày thôi việc (new 26/03/2013)
	date_job_severance_m nvarchar(10),						--ngày thôi việc (new 26/03/2013)
	date_job_severance_y nvarchar(10),						--ngày thôi việc (new 26/03/2013)

	subsidy float,										--trợ cấp thôi việc
	refund_fee_education float,							--bồi hoàn cho phí đào tạo
	compensation float,									--các bồi thường khác
	created_date datetime,
	created_by int,
	modified_date datetime,
	modified_by int
)
go
-----------------------------------------------------
--create table tbl_employee_family				--bảng thân nhân
--(
--	family_id int identity(1,1) primary key,
--	employee_id int references tbl_employee(employee_id),
--	files nvarchar(500),
--	family_status tinyint,
--	created_date datetime,
--	created_by int,
--	modified_date datetime,
--	modified_by int
--)
--go
----------------------------------------------------
create table tbl_employee_medical_records			--bảng hồ sơ y tế
(
	medical_records_id int identity(1,1) primary key,
	employee_id int references tbl_employee(employee_id),
	date_medical_examination datetime,					--ngày khám sức khỏe
	date_medical_examination_d nvarchar(10),					--ngày khám sức khỏe (new 26/03/2013)
	date_medical_examination_m nvarchar(10),					--thangs khám sức khỏe (new 26/03/2013)
	date_medical_examination_y nvarchar(10),					--nawm khám sức khỏe (new 26/03/2013)

	medical_status	nvarchar(100),						--tình trạng sức khỏe
	weights	nvarchar(50),								--cân nặng
	height nvarchar(50),								--chiều cao
	hospital nvarchar(100),								--bệnh viện
	address_examination nvarchar(150),					--địa chỉ khám
	blood_group	nvarchar(30),							--nhóm máu
	major_disease nvarchar(150),						--bệnh chính
	descriptions ntext,
	created_date datetime,
	created_by int,
	modified_date datetime,
	modified_by int	
)
go
---------------------------------------------------
--create table tbl_employee_house_land				--bảng nhà đất
--(
--	house_land_id int identity(1,1) primary key,
--	employee_id int references tbl_employee(employee_id),
--	granted bit,									--cấp
--	bought bit,										--mua
--	general bit,									--ở chung với gia đình
--	forms_of_ownership	nvarchar(150),				--loại hình sở hũu
--	type_of_house nvarchar(150),					--loại nhà
--	area nvarchar(100),								--diện tích
--	house_land_status tinyint,
--	created_date datetime,
--	created_by int,
--	modified_date datetime,
--	modified_by int
--)
--go
----------------------------------------------------
create table tbl_employee_insurance					--hồ sơ bảo hiểm
(
	insurance_id int identity(1,1) primary key,
	employee_id int references tbl_employee(employee_id),
	start_date datetime,								--ngày bắt đầu đóng
	start_date_d nvarchar(10),                            --Ngay bat dau dong bao hiem (new 26/03/2013)
	start_date_m nvarchar(10),                            --Ngay bat dau dong bao hiem (new 26/03/2013)
	start_date_y nvarchar(10),                            --Ngay bat dau dong bao hiem (new 26/03/2013)
	content ntext,										--quá trình đóng
	premium varchar(50),								--mức đóng(phí bảo hiểm)
	number_insurance_book varchar(30),					--số sổ bảo hiểm
	insurance_status tinyint,
	created_date datetime,
	created_by int,
	modified_date datetime,
	modified_by int		
)
go
----------------------------------------------------
create table tbl_employee_foreign_countries			--bảng công tác nước ngoài
(
	foreign_countries int identity(1,1) primary key,
	employee_id int references tbl_employee(employee_id),
	out_date datetime,									--ngày đi
	in_date datetime,									--ngày về
	country	nvarchar(50),								--quốc gia
	reason ntext,										--lý do
	number_decision nvarchar(50),						--quyết định số
	descriptions ntext,
	created_date datetime,
	created_by int,
	modified_date datetime,
	modified_by int
)
go
-----------------------------------------------------
create table tbl_employee_rotation							--quá trình công tác
(
	rotation_id int identity(1,1) primary key,
	employee_id int references tbl_employee(employee_id),
	departments_id int,
	--form_rotation nvarchar(150),							--hình thức luân chuyển
	--date_decision datetime,									--ngày quyết định
	--new_branch_work nvarchar(150),							--đơn vị mới
	--new_positions nvarchar(150),							--chức danh mới
	--years nvarchar(150),										--năm công tác vd: 2001-2010
	levels nvarchar(200),									--cấp bậc(new)
	positions nvarchar(100),								--chức vụ(new)
	branch nvarchar(150),									--đơn vị(new)
	unit nvarchar(200),									    --Đoàn thể
	from_date datetime,
	from_date_d nvarchar(10),                               -- Tu ngay (new 26/03/2013)
	from_date_m nvarchar(10),                               -- Tu ngay (new 26/03/2013)
	from_date_y nvarchar(10),                               -- Tu ngay (new 26/03/2013)

	to_date datetime, 
	to_date_d nvarchar(10),                               -- den ngay (new 26/03/2013)
	to_date_m nvarchar(10),                               -- den ngay (new 26/03/2013)
	to_date_y nvarchar(10),                               -- den ngay (new 26/03/2013)
	
	rotation_status tinyint,
	created_date datetime,
	created_by int,
	modified_date datetime,
	modified_by int
)
go
-------------------------------------------------------------
create table tbl_employee_school							--bảng hồ sơ học vấn nhân viên
(
	employee_school_id int identity(1,1) primary key,
	employee_id int references tbl_employee(employee_id),
	school_id int references tbl_school(school_id),
	courses nvarchar(200),									--khóa học
	majors nvarchar(200),									--chuyên ngành
	study_level nvarchar(50),								--cấp học
	start_date datetime,									--bắt đầu khóa học
	start_date_d nvarchar(10),                            --Ngay bat đầu khóa học (new 26/03/2013)
	start_date_m nvarchar(10),                            --thang bat đầu khóa học (new 26/03/2013)
	start_date_y nvarchar(10),                            --nam bat đầu khóa học (new 26/03/2013)
	end_date datetime,										--kết thúc khóa học
	end_date_d nvarchar(10),										--kết thúc khóa học (new 26/03/2013)
	end_date_m nvarchar(10),										--kết thúc khóa học (new 26/03/2013)
	end_date_y nvarchar(10),										--kết thúc khóa học (new 26/03/2013)

	diplomas nvarchar(150),									--bằng cấp
	descriptions ntext,
	created_date datetime,
	created_by int,
	modified_date datetime,
	modified_by int
)
go
--------------------------------------------------------------------
create table tbl_reward							--bảng khen thưởng
(
	reward_id int identity(1,1) primary key,
	reward_name nvarchar(100),						--tên loại khen thưởng/hình thức kỷ luật
	reward_name_group nvarchar(100),                  --- Tên nhóm khen thưởng/kỷ luật
	descriptions ntext,								--lý do khen/thành tích/lý do kỷ luật
	reward_status tinyint,                  -- trạng thái
	form_reward varchar(100),               --1 Nhà nước,2 chính phủ , 3 BQP ,4..
	type_reward tinyint,                    --1 là khen thưởng ,2 tặng thưởng huân huy chương, 3 là kỷ luật
	skill_reward tinyint,					--1 là chính quyền,2 tổ chức đảng,3 tổ chức quân chủng
	signature_date datetime,				--ngày ký
	signature_name nvarchar(150),			--người ký(cấp quyết định)
	positions nvarchar(150),				--chức danh
	type_medals nvarchar(200),				--loại huân huy chương
	medal_class nvarchar(150),				--hạng huân huy chuong
	Years int,
	from_date datetime,
	to_date datetime,
	employee_id int,						
	departments_id int,
	reward_number nvarchar(50),				--Số quyết định
	is_collective int						--1 là cá nhân, 2 là tập thể		
)
go

----------------------------------------------------------------------
-----Insert data
--------
delete tbl_users
go
DBCC CHECKIDENT (tbl_users, RESEED,1)
go
insert into tbl_users values('quantri@gmail.com','admin','5a384gaQdrf/Lfv+zrksll9ZLk/Vcb25YRmuCT1VVijMdc/6QKPp3wqICsJzmpXO',1,NULL,NULL,'abcd','1a64a')
insert into tbl_users values('nhanvien2@adcc.vn','employee','o6QynU8TKcaO7B8fT10QBhC3DDtK+p1UEGVAkOxLdsDzHcbNPO69ClUgfaozZX77',1,NULL,NULL,'abcd','1a64a')
insert into tbl_users values('nhanvien2@adcc.vn','employee','o6QynU8TKcaO7B8fT10QBhC3DDtK+p1UEGVAkOxLdsDzHcbNPO69ClUgfaozZX77',1,NULL,NULL,'abcd','1a64a')
--------
delete tbl_user_group
go
DBCC CHECKIDENT (tbl_user_group, RESEED,1)
go
insert into tbl_user_group values('admin','Nhóm quản trị phần mềm','true',NULL)
insert into tbl_user_group values('hoidongquantri','Nhóm hội đồng quản trị','true',NULL)		--only view
insert into tbl_user_group values('truongphong','Nhóm trưởng phòng','true',NULL)
insert into tbl_user_group values('nhanvien','Nhóm nhân viên','true',NULL)
--------
delete tbl_page
go
DBCC CHECKIDENT (tbl_page, RESEED,1)
go
insert into tbl_page values(N'Bảng điều khiển',NULL,'/dashboard/index',0,1,1,NULL)
insert into tbl_page values(N'index',NULL,'/dashboard/index',1,0,2,1)
insert into tbl_page values(N'Quản trị',NULL,'/account/list_user',1,1,3,NULL)
insert into tbl_page values(N'Tùy chỉnh cá nhân',NULL,'',1,1,4,3)
insert into tbl_page values(N'Đổi mật khẩu',NULL,'/account/change_password',1,1,5,4)
insert into tbl_page values(N'Tài khoản',NULL,'/account/list_user',1,1,6,3)
insert into tbl_page values(N'Danh sách quản trị viên',NULL,'/account/list_user',1,1,7,6)
insert into tbl_page values(N'Thêm mới quản trị viên',NULL,'/account/add_user',1,1,8,7)
insert into tbl_page values(N'Cập nhật quản trị viên',NULL,'/account/edit_user',1,0,9,7)
insert into tbl_page values(N'Danh sách chức năng',NULL,'/account/list_page',1,1,10,6)
insert into tbl_page values(N'Thêm mới chức năng',NULL,'/account/add_page',1,1,11,10)
insert into tbl_page values(N'Cập nhật chức năng',NULL,'/account/edit_page',1,0,12,10)
insert into tbl_page values(N'Danh sách nhóm',NULL,'/account/list_group',1,1,13,6)
insert into tbl_page values(N'Thêm mới nhóm',NULL,'/account/add_group',1,1,14,13)
insert into tbl_page values(N'Cập nhật nhóm',NULL,'/account/edit_group',1,0,15,13)
insert into tbl_page values(N'Phân quyền truy cập',NULL,'/account/access_page',1,0,16,13)
insert into tbl_page values(N'Nội dung',NULL,'/h_content/list_school',0,1,17,NULL)
insert into tbl_page values(N'Danh sách trường đào tạo',NULL,'/h_content/list_school',1,1,18,17)
insert into tbl_page values(N'Thêm mới trường đào tạo',NULL,'/h_content/add_school',1,1,19,18)
insert into tbl_page values(N'Cập nhật trường đào tạo',NULL,'/h_content/edit_school',1,0,20,18)
insert into tbl_page values(N'Đối tượng nhân viên',NULL,'/h_content/list_object_employee',1,1,21,17)
insert into tbl_page values(N'Thêm mới đối tượng NV',NULL,'/h_content/add_object_employee',1,1,22,21)
insert into tbl_page values(N'Cập nhật đối tượng NV',NULL,'/h_content/edit_object_employee',1,0,23,21)
insert into tbl_page values(N'Loại hợp đồng lao động',NULL,'/h_content/list_type_contract',1,1,24,17)
insert into tbl_page values(N'Thêm mới loại hợp đồng',NULL,'/h_content/add_type_contract',1,1,25,24)
insert into tbl_page values(N'Cập nhật loại hợp đồng',NULL,'/h_content/edit_type_contract',1,0,26,24)
insert into tbl_page values(N'Danh sách phòng ban',NULL,'/h_content/list_departments',1,1,27,17)
insert into tbl_page values(N'Thêm mới phòng ban',NULL,'/h_content/add_departments',1,1,28,27)
insert into tbl_page values(N'Cập nhật thông tin phòng ban',NULL,'/h_content/edit_departments',1,0,29,27)
insert into tbl_page values(N'Loại hình nghỉ phép',NULL,'/h_content/list_type_leave',1,1,30,17)
insert into tbl_page values(N'Thêm mới loại hình nghỉ phép',NULL,'/h_content/add_type_leave',1,1,31,30)
insert into tbl_page values(N'Cập nhật loại hình nghỉ phép',NULL,'/h_content/edit_type_leave',1,0,32,30)
insert into tbl_page values(N'Hồ sơ',NULL,'/profile/profile_search',1,1,33,NULL)
insert into tbl_page values(N'Quản lý hồ sơ',NULL,'/profile/profile_search',1,1,34,33)
insert into tbl_page values(N'Danh sách NV sắp hết hạn HĐ',NULL,'/profile/list_employee_contract',1,1,35,34)
insert into tbl_page values(N'Hồ sơ sức khỏe',NULL,'/profile/list_employee_medical_records',1,1,36,34)
insert into tbl_page values(N'Hồ sơ NV thôi việc',NULL,'/profile/list_employee_job_severance',1,1,37,34)
insert into tbl_page values(N'Hồ sơ bảo hiểm',NULL,'/profile/list_employee_insurance',1,1,38,34)
insert into tbl_page values(N'Hồ sơ visa-passport',NULL,'/profile/list_employee_visa_passport',1,1,39,34)
insert into tbl_page values(N'Hồ sơ chi tiết',NULL,'/profile/detail_profile',1,0,40,34)
insert into tbl_page values(N'Nghỉ phép',NULL,'/profile/leave_manage',1,1,41,40)
insert into tbl_page values(N'Thêm hồ sơ mới',NULL,'/h_content/add_employee',1,1,42,33)
insert into tbl_page values(N'Cập nhật hồ sơ',NULL,'/h_content/edit_employee',1,0,43,40)
insert into tbl_page values(N'Thêm thân nhân mới',NULL,'/h_content/add_employee_family',1,0,44,40)
insert into tbl_page values(N'Cập nhật hồ sơ thân nhân',NULL,'/h_content/edit_employee_family',1,0,45,40)
insert into tbl_page values(N'Thêm hồ sơ bảo hiểm',NULL,'/h_content/add_employee_insurance',1,0,46,40)
insert into tbl_page values(N'Cập nhật hồ sơ bảo hiểm',NULL,'/h_content/edit_employee_insurance',1,0,47,40)
insert into tbl_page values(N'Quá trình công tác',NULL,'/h_content/add_employee_rotation',1,0,48,40)
insert into tbl_page values(N'Cập nhật quá trình công tác',NULL,'/h_content/edit_employee_rotation',1,0,49,40)
insert into tbl_page values(N'Thêm Quá trình đào tạo',NULL,'/h_content/add_employee_school',1,0,50,40)
insert into tbl_page values(N'Cập nhật quá trình đào tạo',NULL,'/h_content/edit_employee_school',1,0,51,40)
insert into tbl_page values(N'Khen thưởng',NULL,'/h_content/add_reward',1,1,52,NULL)
insert into tbl_page values(N'Quá trình công tác nước ngoài',NULL,'/h_content/list_employee_foreign_countries',1,0,53,40)
insert into tbl_page values(N'Thêm hồ sơ công tác nước ngoài',NULL,'/h_content/add_employee_foreign_countries',1,0,54,53)
insert into tbl_page values(N'Cập nhật hồ sơ công tác nước ngoài',NULL,'/h_content/edit_employee_foreign_countries',1,0,55,53)
insert into tbl_page values(N'Quản lý nghỉ phép',NULL,'/h_content/list_leave_manage',1,0,56,17)
insert into tbl_page values(N'Thêm hồ sơ nghỉ phép',NULL,'/h_content/add_leave_manage',1,0,57,56)
insert into tbl_page values(N'Cập nhật hồ sơ nghỉ phép',NULL,'/h_content/edit_leave_manage',1,0,58,56)
insert into tbl_page values(N'Quản lý visa-passport',NULL,'/h_content/list_employee_visa_passport',1,0,59,40)
insert into tbl_page values(N'Thêm hồ sơ visa-passport',NULL,'/h_content/add_employee_visa_passport',1,0,60,59)
insert into tbl_page values(N'Cập nhật hồ sơ visa-passport',NULL,'/h_content/edit_employee_visa_passport',1,0,61,59)
insert into tbl_page values(N'Thêm khen thưởng',NULL,'/h_content/add_reward',1,1,62,52)
insert into tbl_page values(N'Cập nhật khen thưởng',NULL,'/h_content/edit_reward',1,0,63,52)

insert into tbl_page values(N'Tặng thưởng huân huy chương ',NULL,'/h_content/list_medals_group',1,1,64,17)
insert into tbl_page values(N'Danh sach tặng cá nhân ',NULL,'/h_content/list_medals',1,1,65,64)
insert into tbl_page values(N'Danh sách tặng tập thể',NULL,'/h_content/list_medals_group',1,1,66,64)
insert into tbl_page values(N'Thêm tặng thưởng',NULL,'/h_content/add_medals',1,1,67,64)
insert into tbl_page values(N'Cập nhật tặng thưởng HH chương',NULL,'/h_content/edit_medals',1,0,68,64)

insert into tbl_page values(N'Danh sách Kỷ luật',NULL,'/h_content/list_discripline',1,1,69,17)
insert into tbl_page values(N'Thêm kỷ luật',NULL,'/h_content/add_discripline',1,1,70,69)
insert into tbl_page values(N'Cập nhật kỷ luật',NULL,'/h_content/edit_discripline',1,0,71,69)
--insert into tbl_page values(N'Báo cáo',NULL,NULL,1,1,73,NULL)
insert into tbl_page values(N'Loại huân huy chương',NULL,'/h_content/list_type_reward',1,1,72,52)
insert into tbl_page values(N'Cập nhật huân huy chương',NULL,'/h_content/edit_type_reward',1,1,73,52)
insert into tbl_page values(N'Thêm huân huy chương',NULL,'/h_content/add_type_reward',1,1,74,52)
insert into tbl_page values(N'Danh sách QNCN_CNVQP',NULL,'/profile/list_QNCN_CNVQP',1,1,75,33)
insert into tbl_page values(N'Danh sách Diễn giải cấp bậc quân hàm',NULL,'/profile/list_DG_CB_QH',1,1,76,33)

insert into tbl_page values(N'Danh sách khen thưởng',NULL,'/profile/list_KT_CN',1,1,77,17)
insert into tbl_page values(N'Khen thưởng cá nhân',NULL,'/profile/list_KT_CN',1,1,78,77)
insert into tbl_page values(N'Khen thưởng tập thể',NULL,'/profile/list_KT_TT',1,1,79,77)
insert into tbl_page values(N'Thêm khen thưởng',NULL,'/h_content/add_reward',1,1,80,77)
insert into tbl_page values(N'Thêm hồ sơ thôi việc',NULL,'/h_content/add_employee_jobSeverance',1,0,81,34)
insert into tbl_page values(N'Cập nhật hồ sơ thôi việc',NULL,'/h_content/edit_employee_jobSeverance',1,0,82,34)
insert into tbl_page values(N'Thêm hồ sơ sức khỏe',NULL,'/h_content/add_employee_medicalRecord',1,0,83,34)
insert into tbl_page values(N'Cập nhật hồ sức khỏe',NULL,'/h_content/edit_employee_medicalRecord',1,0,84,34)
insert into tbl_page values(N'Thêm hợp đồng',NULL,'/h_content/add_employee_contract',1,0,85,34)
insert into tbl_page values(N'Cập nhật hợp đồng',NULL,'/h_content/edit_employee_contract',1,0,86,34)

insert into tbl_page values(N'Xóa hồ sơ',NULL,'/profile/delete_employee',1,0,87,34)
insert into tbl_page values(N'Xóa hồ sơ y tế',NULL,'/profile/delete_employee_medical_records',1,0,88,33)
insert into tbl_page values(N'Xóa hồ sơ thân nhân',NULL,'/profile/delete_employee_family',1,0,89,33)
insert into tbl_page values(N'Xóa hồ sơ bảo hiểm',NULL,'/profile/delete_employee_insurance',1,0,90,33)
insert into tbl_page values(N'Xóa quá trình đào tạo',NULL,'/profile/delete_employee_school',1,0,91,33)
insert into tbl_page values(N'Xóa hồ sơ thông tin visa/passport',NULL,'/profile/delete_employee_visa_passport',1,0,92,33)
insert into tbl_page values(N'Xóa thông tin hợp đồng',NULL,'/profile/delete_contract',1,0,93,33)
insert into tbl_page values(N'Xóa thông tin nghỉ việc',NULL,'/profile/delete_employee_job_severance',1,0,94,33)

insert into tbl_page values(N'Xóa loại đối tượng nhân viên',NULL,'/profile/delete_object_employee',1,0,95,21)
insert into tbl_page values(N'Xóa loại hợp đồng',NULL,'/profile/delete_type_contract',1,0,96,24)
insert into tbl_page values(N'Xóa loại hình nghỉ phép',NULL,'/profile/delete_type_leave',1,0,97,30)
insert into tbl_page values(N'Xóa loại hợp đồng',NULL,'/profile/delete_type_reward',1,0,98,72)

insert into tbl_page values(N'Xóa khen thưởng',NULL,'/profile/delete_reward',1,0,99,77)
insert into tbl_page values(N'Xóa kỷ luật',NULL,'/profile/delete_reward',1,0,100,69)
insert into tbl_page values(N'Xóa tặng thưởng huân huy chương',NULL,'/profile/delete_reward',1,0,101,64)
insert into tbl_page values(N'Xóa tặng thưởng',NULL,'/profile/delete_reward',1,0,102,64)

insert into tbl_page values(N'Hồ sơ thân nhân',NULL,'/h_content/list_employee_family',1,1,103,17)
insert into tbl_page values(N'Báo cáo tổng khen thưởng cá nhân',NULL,'/profile/list_KT_CN_total',1,1,104,77)
insert into tbl_page values(N'Báo cáo tổng khen thưởng tập thể',NULL,'/profile/list_KT_TT_total',1,1,105,77)
--------
delete tbl_page_role
go
DBCC CHECKIDENT (tbl_page_role, RESEED,1)
go
insert into tbl_page_role values(1,1,NULL,'2012-08-27 14:38:27.017')
insert into tbl_page_role values(2,1,NULL,'2012-09-04 00:00:00.000')
insert into tbl_page_role values(3,1,NULL,'2012-09-04 00:00:00.000')
insert into tbl_page_role values(4,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(5,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(6,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(7,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(8,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(9,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(10,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(11,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(12,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(13,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(14,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(15,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(16,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(17,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(18,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(19,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(20,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(21,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(22,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(23,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(24,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(25,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(26,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(27,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(28,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(29,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(30,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(31,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(32,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(33,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(34,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(35,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(36,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(37,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(38,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(39,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(40,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(41,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(42,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(43,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(44,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(45,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(46,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(47,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(48,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(49,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(50,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(51,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(52,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(53,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(54,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(55,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(56,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(57,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(58,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(59,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(60,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(61,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(62,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(63,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(64,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(65,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(66,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(67,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(68,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(69,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(70,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(71,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(72,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(73,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(74,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(75,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(76,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(77,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(78,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(79,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(80,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(81,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(82,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(83,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(84,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(85,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(86,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(87,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(88,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(89,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(90,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(91,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(92,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(93,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(94,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(95,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(96,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(97,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(98,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(99,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(100,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(101,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(102,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(103,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(104,1,NULL,'2012-09-09 00:00:00.000')
insert into tbl_page_role values(105,1,NULL,'2012-09-09 00:00:00.000')
--------
delete tbl_user_ref_roles
go
DBCC CHECKIDENT (tbl_user_ref_roles, RESEED,1)
go
insert into tbl_user_ref_roles values('1','1',NULL,NULL)
insert into tbl_user_ref_roles values('2','2',NULL,NULL)
------------
delete tbl_type_leave
go
DBCC CHECKIDENT (tbl_type_leave, RESEED,1)
go
insert into tbl_type_leave values(N'Nghỉ phép có lương',N'Là loại hình nghỉ phép có thông báo trước và có lương trong quá trình nghỉ')
insert into tbl_type_leave values(N'Nghỉ thai sản',N'Là loại hình nghỉ khi nhân viên mang thai và nghỉ để sinh con')
insert into tbl_type_leave values(N'Nghỉ không lương',N'Những ngày nghỉ ngoài quy định khi được phép nghỉ')
insert into tbl_type_leave values(N'Nghỉ đột xuất',N'Nhà có ma cha hoặc .....')
insert into tbl_type_leave values(N'Nghỉ đau ốm',N'Ốm đau không thể đi làm được...')

--------
delete tbl_type_contract
go
DBCC CHECKIDENT (tbl_type_contract, RESEED,1)
go
insert into tbl_type_contract values(N'Hợp đồng thử việc',N'Là loại hợp đồng ký cho nhân viên thử việc.')
insert into tbl_type_contract values(N'Hợp đồng dài hạn',N'Những loại hợp đồng ký dài ngày. Ví dụ từ 6tháng trở lên')
insert into tbl_type_contract values(N'Hợp đồng ngắn hạn',N'Những loại hợp đồng ký ngắn ngày')
---------
delete tbl_object_employee
go
DBCC CHECKIDENT (tbl_object_employee, RESEED,1)
go

insert into tbl_object_employee values(N'Cơ quan cán bộ quản lý',NULL,NULL)
insert into tbl_object_employee values(N'Sỹ quan',NULL,1)
insert into tbl_object_employee values(N'*',NULL,2)
insert into tbl_object_employee values(N'4//',NULL,2)
insert into tbl_object_employee values(N'3//',NULL,2)
insert into tbl_object_employee values(N'2//',NULL,2)
insert into tbl_object_employee values(N'1//',NULL,2)
insert into tbl_object_employee values(N'1/',NULL,2)
insert into tbl_object_employee values(N'2/',NULL,2)
insert into tbl_object_employee values(N'3/',NULL,2)
insert into tbl_object_employee values(N'4/',NULL,2)
insert into tbl_object_employee values(N'Diện khác',NULL,1)
insert into tbl_object_employee values(N'*',NULL,12)
insert into tbl_object_employee values(N'QNCN',NULL,12)
insert into tbl_object_employee values(N'CCQP',NULL,12)
insert into tbl_object_employee values(N'Cơ quan quân lực quản lý',NULL,NULL)
insert into tbl_object_employee values(N'QNCN',NULL,16)
insert into tbl_object_employee values(N'*',NULL,17)
insert into tbl_object_employee values(N'4//',NULL,17)
insert into tbl_object_employee values(N'3//',NULL,17)
insert into tbl_object_employee values(N'2//',NULL,17)
insert into tbl_object_employee values(N'1//',NULL,17)
insert into tbl_object_employee values(N'1/',NULL,17)
insert into tbl_object_employee values(N'2/',NULL,17)
insert into tbl_object_employee values(N'3/',NULL,17)
insert into tbl_object_employee values(N'4/',NULL,17)
insert into tbl_object_employee values(N'QNVQP',NULL,16)
---------
delete tbl_departments
go
DBCC CHECKIDENT (tbl_departments, RESEED,1)
go
insert into tbl_departments values(N'Ban giám đốc',Null,1,null)
insert into tbl_departments values(N'Kiểm soát viên',Null,1,null)
insert into tbl_departments values(N'Phòng KH-KD',Null,1,null)
insert into tbl_departments values(N'Phòng chính trị',Null,1,null)
insert into tbl_departments values(N'Phòng TC-LĐ',Null,1,null)
insert into tbl_departments values(N'Phòng kỹ thuật',Null,1,null)
insert into tbl_departments values(N'Văn phòng',Null,1,null)
insert into tbl_departments values(N'Xí nghiệp TKGT1',Null,1,null)
insert into tbl_departments values(N'Xí nghiệp TKGT2',Null,1,null)
insert into tbl_departments values(N'Xí nghiệp TKDD&CN',Null,1,null)
insert into tbl_departments values(N'Xí nghiệp XDCT',Null,1,null)
insert into tbl_departments values(N'XNĐT&KDBĐS	',Null,1,null)
insert into tbl_departments values(N'Trung tâm KT-ĐT',Null,1,null)
insert into tbl_departments values(N'Trung tâm KS-KD',Null,1,null)
insert into tbl_departments values(N'Chi nhánh miền trung',Null,1,null)
insert into tbl_departments values(N'Chi nhánh miền nam',Null,1,null)
insert into tbl_departments values(N'Đội dự án QP',Null,1,null)
-----------
delete tbl_employee
go
DBCC CHECKIDENT (tbl_employee, RESEED,1)
go
INSERT INTO tbl_employee VALUES(N'N001',N'Nguyen Van A',N'nguyenvana',14,2,1989,'1989-02-14','0973493','02943243',N'bankacc',N'bank-name','taxtcode',N'family_red',N'house','email',1,N'dantoc',N'tongiao',N'hometown',N'current',N'hosehol','26','03','2013','2013-03-26','26','03','2013','2013-03-26',N'ranks',N'salarry',N'possit',N'unit','30','03','2010','2010-03-30',N'marrital',N'serrial',N'serrail_num_mili',N'identiticard','20','02','1998','1998-02-20',N'palce',N'arrmy','15','05','1992','1992-05-15',N'issue',N'cultu',N'Nngu','07','04','2010','2010-04-07',1,1,N'avata','2013-03-27',1,null,null,N'noi dung mo ta')

INSERT INTO tbl_employee VALUES(N'N001',N'Nguyễn Văn Hùng 1',N'nguyenvantung',14,2,1989,'1989-02-14','0435.560.152','0912596499',N'98759009',N'Agribank','0105605115',N'ten file',N'1','hung@adcc.com.vn',1,N'Kinh',N'Đạo thiên chúa',N'Tĩnh Gia - Nghệ An',N'Thanh Xuân - Hà Nội',N'Tĩnh Gia - Nghệ An','26','03','2013','2013-03-26','26','03','2013','2013-03-26',N'Thiếu tá',N'4',N'Phó giám đốc',null,'30','03','2010','2010-03-30',N'Đã có vợ',N'01015536',N'010234334433',N'1215969342','20','02','1998','1998-02-20',N'palce',N'arrmy','15','05','1992','1992-05-15',N'Quân đội nhân dân VN',N'Đại học',N'A','07','04','2010','2010-04-07',1,1,N'avata','2013-03-27',1,null,null,N'noi dung mo ta')
INSERT INTO tbl_employee VALUES(N'N002',N'Nguyễn Văn Hùng 2',N'nguyenvantung',14,2,1989,'1989-02-14','0435.560.152','0912596499',N'98759009',N'Agribank','0105605115',N'ten file',N'1','hung@adcc.com.vn',1,N'Kinh',N'Đạo thiên chúa',N'Tĩnh Gia - Nghệ An',N'Thanh Xuân - Hà Nội',N'Tĩnh Gia - Nghệ An','26','03','2013','2013-03-26','26','03','2013','2013-03-26',N'Thiếu tá',N'4',N'Phó giám đốc',null,'30','03','2010','2010-03-30',N'Đã có vợ',N'01015536',N'010234334433',N'1215969342','20','02','1998','1998-02-20',N'palce',N'arrmy','15','05','1992','1992-05-15',N'Quân đội nhân dân VN',N'Đại học',N'A','07','04','2010','2010-04-07',1,1,N'avata','2013-03-27',1,null,null,N'noi dung mo ta')
INSERT INTO tbl_employee VALUES(N'N003',N'Nguyễn Văn Hùng 3',N'nguyenvantung',14,2,1989,'1989-02-14','0435.560.152','0912596499',N'98759009',N'Agribank','0105605115',N'ten file',N'1','hung@adcc.com.vn',1,N'Kinh',N'Đạo thiên chúa',N'Tĩnh Gia - Nghệ An',N'Thanh Xuân - Hà Nội',N'Tĩnh Gia - Nghệ An','26','03','2013','2013-03-26','26','03','2013','2013-03-26',N'Thiếu tá',N'4',N'Phó giám đốc',null,'30','03','2010','2010-03-30',N'Đã có vợ',N'01015536',N'010234334433',N'1215969342','20','02','1998','1998-02-20',N'palce',N'arrmy','15','05','1992','1992-05-15',N'Quân đội nhân dân VN',N'Đại học',N'A','07','04','2010','2010-04-07',1,1,N'avata','2013-03-27',1,null,null,N'noi dung mo ta')
INSERT INTO tbl_employee VALUES(N'N004',N'Nguyễn Văn Hùng 4',N'nguyenvantung',14,2,1989,'1989-02-14','0435.560.152','0912596499',N'98759009',N'Agribank','0105605115',N'ten file',N'1','hung@adcc.com.vn',1,N'Kinh',N'Đạo thiên chúa',N'Tĩnh Gia - Nghệ An',N'Thanh Xuân - Hà Nội',N'Tĩnh Gia - Nghệ An','26','03','2013','2013-03-26','26','03','2013','2013-03-26',N'Thiếu tá',N'4',N'Phó giám đốc',null,'30','03','2010','2010-03-30',N'Đã có vợ',N'01015536',N'010234334433',N'1215969342','20','02','1998','1998-02-20',N'palce',N'arrmy','15','05','1992','1992-05-15',N'Quân đội nhân dân VN',N'Đại học',N'A','07','04','2010','2010-04-07',1,1,N'avata','2013-03-27',1,null,null,N'noi dung mo ta')
INSERT INTO tbl_employee VALUES(N'N005',N'Nguyễn Văn Hùng 5',N'nguyenvantung',14,2,1989,'1989-02-14','0435.560.152','0912596499',N'98759009',N'Agribank','0105605115',N'ten file',N'1','hung@adcc.com.vn',1,N'Kinh',N'Đạo thiên chúa',N'Tĩnh Gia - Nghệ An',N'Thanh Xuân - Hà Nội',N'Tĩnh Gia - Nghệ An','26','03','2013','2013-03-26','26','03','2013','2013-03-26',N'Thiếu tá',N'4',N'Phó giám đốc',null,'30','03','2010','2010-03-30',N'Đã có vợ',N'01015536',N'010234334433',N'1215969342','20','02','1998','1998-02-20',N'palce',N'arrmy','15','05','1992','1992-05-15',N'Quân đội nhân dân VN',N'Đại học',N'A','07','04','2010','2010-04-07',1,1,N'avata','2013-03-27',1,null,null,N'noi dung mo ta')
INSERT INTO tbl_employee VALUES(N'N006',N'Nguyễn Văn Hùng 6',N'nguyenvantung',14,2,1989,'1989-02-14','0435.560.152','0912596499',N'98759009',N'Agribank','0105605115',N'ten file',N'1','hung@adcc.com.vn',1,N'Kinh',N'Đạo thiên chúa',N'Tĩnh Gia - Nghệ An',N'Thanh Xuân - Hà Nội',N'Tĩnh Gia - Nghệ An','26','03','2013','2013-03-26','26','03','2013','2013-03-26',N'Thiếu tá',N'4',N'Phó giám đốc',null,'30','03','2010','2010-03-30',N'Đã có vợ',N'01015536',N'010234334433',N'1215969342','20','02','1998','1998-02-20',N'palce',N'arrmy','15','05','1992','1992-05-15',N'Quân đội nhân dân VN',N'Đại học',N'A','07','04','2010','2010-04-07',1,1,N'avata','2013-03-27',1,null,null,N'noi dung mo ta')
INSERT INTO tbl_employee VALUES(N'N007',N'Nguyễn Văn Hùng 7',N'nguyenvantung',14,2,1989,'1989-02-14','0435.560.152','0912596499',N'98759009',N'Agribank','0105605115',N'ten file',N'1','hung@adcc.com.vn',1,N'Kinh',N'Đạo thiên chúa',N'Tĩnh Gia - Nghệ An',N'Thanh Xuân - Hà Nội',N'Tĩnh Gia - Nghệ An','26','03','2013','2013-03-26','26','03','2013','2013-03-26',N'Thiếu tá',N'4',N'Phó giám đốc',null,'30','03','2010','2010-03-30',N'Đã có vợ',N'01015536',N'010234334433',N'1215969342','20','02','1998','1998-02-20',N'palce',N'arrmy','15','05','1992','1992-05-15',N'Quân đội nhân dân VN',N'Đại học',N'A','07','04','2010','2010-04-07',1,1,N'avata','2013-03-27',1,null,null,N'noi dung mo ta')
INSERT INTO tbl_employee VALUES(N'N008',N'Nguyễn Văn Hùng 8',N'nguyenvantung',14,2,1989,'1989-02-14','0435.560.152','0912596499',N'98759009',N'Agribank','0105605115',N'ten file',N'1','hung@adcc.com.vn',1,N'Kinh',N'Đạo thiên chúa',N'Tĩnh Gia - Nghệ An',N'Thanh Xuân - Hà Nội',N'Tĩnh Gia - Nghệ An','26','03','2013','2013-03-26','26','03','2013','2013-03-26',N'Thiếu tá',N'4',N'Phó giám đốc',null,'30','03','2010','2010-03-30',N'Đã có vợ',N'01015536',N'010234334433',N'1215969342','20','02','1998','1998-02-20',N'palce',N'arrmy','15','05','1992','1992-05-15',N'Quân đội nhân dân VN',N'Đại học',N'A','07','04','2010','2010-04-07',1,1,N'avata','2013-03-27',1,null,null,N'noi dung mo ta')
INSERT INTO tbl_employee VALUES(N'N009',N'Nguyễn Văn Hùng 9',N'nguyenvantung',14,2,1989,'1989-02-14','0435.560.152','0912596499',N'98759009',N'Agribank','0105605115',N'ten file',N'1','hung@adcc.com.vn',1,N'Kinh',N'Đạo thiên chúa',N'Tĩnh Gia - Nghệ An',N'Thanh Xuân - Hà Nội',N'Tĩnh Gia - Nghệ An','26','03','2013','2013-03-26','26','03','2013','2013-03-26',N'Thiếu tá',N'4',N'Phó giám đốc',null,'30','03','2010','2010-03-30',N'Đã có vợ',N'01015536',N'010234334433',N'1215969342','20','02','1998','1998-02-20',N'palce',N'arrmy','15','05','1992','1992-05-15',N'Quân đội nhân dân VN',N'Đại học',N'A','07','04','2010','2010-04-07',1,1,N'avata','2013-03-27',1,null,null,N'noi dung mo ta')
INSERT INTO tbl_employee VALUES(N'N010',N'Nguyễn Văn Hùng 10',N'nguyenvantung',14,2,1989,'1989-02-14','0435.560.152','0912596499',N'98759009',N'Agribank','0105605115',N'ten file',N'1','hung@adcc.com.vn',1,N'Kinh',N'Đạo thiên chúa',N'Tĩnh Gia - Nghệ An',N'Thanh Xuân - Hà Nội',N'Tĩnh Gia - Nghệ An','26','03','2013','2013-03-26','26','03','2013','2013-03-26',N'Thiếu tá',N'4',N'Phó giám đốc',null,'30','03','2010','2010-03-30',N'Đã có vợ',N'01015536',N'010234334433',N'1215969342','20','02','1998','1998-02-20',N'palce',N'arrmy','15','05','1992','1992-05-15',N'Quân đội nhân dân VN',N'Đại học',N'A','07','04','2010','2010-04-07',1,1,N'avata','2013-03-27',1,null,null,N'noi dung mo ta')
INSERT INTO tbl_employee VALUES(N'N011',N'Nguyễn Văn Hùng 11',N'nguyenvantung',14,2,1989,'1989-02-14','0435.560.152','0912596499',N'98759009',N'Agribank','0105605115',N'ten file',N'1','hung@adcc.com.vn',1,N'Kinh',N'Đạo thiên chúa',N'Tĩnh Gia - Nghệ An',N'Thanh Xuân - Hà Nội',N'Tĩnh Gia - Nghệ An','26','03','2013','2013-03-26','26','03','2013','2013-03-26',N'Thiếu tá',N'4',N'Phó giám đốc',null,'30','03','2010','2010-03-30',N'Đã có vợ',N'01015536',N'010234334433',N'1215969342','20','02','1998','1998-02-20',N'palce',N'arrmy','15','05','1992','1992-05-15',N'Quân đội nhân dân VN',N'Đại học',N'A','07','04','2010','2010-04-07',1,1,N'avata','2013-03-27',1,null,null,N'noi dung mo ta')
INSERT INTO tbl_employee VALUES(N'N012',N'Nguyễn Văn Hùng 12',N'nguyenvantung',14,2,1989,'1989-02-14','0435.560.152','0912596499',N'98759009',N'Agribank','0105605115',N'ten file',N'1','hung@adcc.com.vn',1,N'Kinh',N'Đạo thiên chúa',N'Tĩnh Gia - Nghệ An',N'Thanh Xuân - Hà Nội',N'Tĩnh Gia - Nghệ An','26','03','2013','2013-03-26','26','03','2013','2013-03-26',N'Thiếu tá',N'4',N'Phó giám đốc',null,'30','03','2010','2010-03-30',N'Đã có vợ',N'01015536',N'010234334433',N'1215969342','20','02','1998','1998-02-20',N'palce',N'arrmy','15','05','1992','1992-05-15',N'Quân đội nhân dân VN',N'Đại học',N'A','07','04','2010','2010-04-07',1,1,N'avata','2013-03-27',1,null,null,N'noi dung mo ta')


GO
---------
delete tbl_department_employee
go
DBCC CHECKIDENT (tbl_department_employee, RESEED,1)
go
INSERT INTO tbl_department_employee VALUES(1,1)
INSERT INTO tbl_department_employee VALUES(2,1)
INSERT INTO tbl_department_employee VALUES(3,1)
INSERT INTO tbl_department_employee VALUES(3,2)
INSERT INTO tbl_department_employee VALUES(4,2)
INSERT INTO tbl_department_employee VALUES(5,3)
INSERT INTO tbl_department_employee VALUES(5,3)
INSERT INTO tbl_department_employee VALUES(5,3)
INSERT INTO tbl_department_employee VALUES(5,3)
INSERT INTO tbl_department_employee VALUES(5,4)
INSERT INTO tbl_department_employee VALUES(6,4)
INSERT INTO tbl_department_employee VALUES(7,5)
INSERT INTO tbl_department_employee VALUES(8,5)
INSERT INTO tbl_department_employee VALUES(9,6)
INSERT INTO tbl_department_employee VALUES(10,7)
INSERT INTO tbl_department_employee VALUES(11,8)
INSERT INTO tbl_department_employee VALUES(12,9)
INSERT INTO tbl_department_employee VALUES(13,10)
INSERT INTO tbl_department_employee VALUES(15,10)
INSERT INTO tbl_department_employee VALUES(16,11)
INSERT INTO tbl_department_employee VALUES(17,13)

go

delete tbl_school
go
DBCC CHECKIDENT (tbl_school, RESEED,1)
go
INSERT INTO tbl_school VALUES(N'Đại học Xây dựng',N'Giải Phóng-Hà Nội',N'Trường Đào Tạo chuyên về xây dựng')
INSERT INTO tbl_school VALUES(N'Đại học Bách khoa',N'Mặt đường giải phóng',N'Trường Đào Tạo nhiều chuyên ngành')
INSERT INTO tbl_school VALUES(N'Đại học Kiến trức',N'Đường Nguyễn Trẫo',N'Trường Đào Tạo chuyên về xây dựng')
INSERT INTO tbl_school VALUES(N'Học viện mật mã',N'Đường Nguyễn Trãi',N'Trường Đào Tạo nhiều chuyên ngành')
INSERT INTO tbl_school VALUES(N'Đại học FPT',N'Hà Nội Về Láng Hòa Lạc',N'Trường Đào Tạo chuyên nghành')
INSERT INTO tbl_school VALUES(N'Đại học Hành chính',N'Giải Phóng-Hà Nội',N'Trường Đào Tạo chuyên về chuyên ngành')
INSERT INTO tbl_school VALUES(N'Cao đẳng Xây dựng',N'Giải Phóng-Hà Nội',N'Trường Đào Tạo chuyên về xây dựng')
INSERT INTO tbl_school VALUES(N'Đại học Giao Thông',N'Giải Phóng-Hà Nội',N'Trường Đào Tạo chuyên về Giao Thông')

----------------
delete tbl_type_reward
go
DBCC CHECKIDENT (tbl_type_reward, RESEED,1)
go
INSERT INTO tbl_type_reward VALUES(N'Huân chương lao động',N'Loại này là huân chương',NULL)
INSERT INTO tbl_type_reward VALUES(N'Hạng Nhất',N'hạng 1',1)
INSERT INTO tbl_type_reward VALUES(N'Hạng Hai',N'hạng 2',1)
INSERT INTO tbl_type_reward VALUES(N'Hạng Ba',N'hạng 3',1)
INSERT INTO tbl_type_reward VALUES(N'Huy chương chiến sĩ thi đua',N'Huy chương',NULL)
INSERT INTO tbl_type_reward VALUES(N'Hạng Nhất',N'hạng 1',5)
INSERT INTO tbl_type_reward VALUES(N'Hạng Hai',N'hạng 2',5)
INSERT INTO tbl_type_reward VALUES(N'Hạng Ba',N'hạng 3',5)
go
----------------------------------------------------------------------------------------
delete tbl_reward
go
DBCC CHECKIDENT (tbl_reward, RESEED,1)
--Mẫu
--INSERT INTO tbl_reward VALUES(reward_name,reward_name_group,descriptions,reward_status,form_reward,type_reward,skill_reward,signature_date,signature_name,positions,type_medals,medal_class,Years,from_date,to_date,employee_id,departments_id,reward_number,is_collective)
--Khen thưởng huân huy chương cho tập thể
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng tập thể...',1,1,1,1,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,Null,3,46466,2)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng Tập thể...',1,1,1,1,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,Null,4,46466,2)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng tập thể...',1,2,1,2,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,Null,5,46466,2)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng tập thể',1,2,1,2,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,Null,6,46466,2)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng tập thể',1,3,1,3,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,Null,4,46466,2)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng tập thể',1,11,1,2,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,Null,6,46466,2)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng tập thể',1,5,1,2,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,Null,7,46466,2)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng tập thể',1,6,1,3,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,Null,8,46466,2)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng tập thể',1,6,1,3,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,Null,9,46466,2)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng tập thể',1,6,1,1,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,Null,7,46466,2)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng tập thể',1,7,1,1,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,Null,6,46466,2)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng tập thể',1,8,1,2,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,Null,5,46466,2)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng tập thể',1,9,1,2,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,Null,6,46466,2)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng tập thể',1,10,1,2,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,Null,6,46466,2)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng tập thể',1,4,1,3,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,Null,4,46466,2)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng tập thể',1,12,1,3,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,Null,2,46466,2)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng tập thể',1,5,1,3,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,Null,3,46466,2)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng tập thể',1,7,1,3,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,Null,2,46466,2)

--Khen thưởng huân huy chương cho cá nhân
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng cá nhân',1,1,1,1,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,1,Null,46466,1)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng cá nhân',1,1,1,1,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,1,Null,46466,1)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng cá nhân',1,2,1,2,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,2,Null,46466,1)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng cá nhân',1,2,1,2,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,2,Null,46466,1)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng cá nhân',1,3,1,3,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,3,Null,46466,1)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng cá nhân',1,11,1,2,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,4,Null,46466,1)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng cá nhân',1,5,1,2,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,5,Null,46466,1)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng cá nhân',1,6,1,3,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,6,Null,46466,1)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng cá nhân',1,6,1,3,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,7,Null,46466,1)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng cá nhân',1,6,1,1,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,8,Null,46466,1)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng cá nhân',1,7,1,1,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,9,Null,46466,1)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng cá nhân',1,8,1,2,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,10,Null,46466,1)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng cá nhân',1,9,1,2,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,1,Null,46466,1)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng cá nhân',1,10,1,2,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,1,Null,46466,1)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng cá nhân',1,4,1,3,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,11,Null,46466,1)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng cá nhân',1,12,1,3,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,12,Null,46466,1)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng cá nhân',1,5,1,3,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,13,Null,46466,1)
INSERT INTO tbl_reward VALUES(Null,Null,N'Mô tả khen thưởng cá nhân',1,7,1,3,'10-10-2012',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-10-2012',Null,14,Null,46466,1)

--Tặng thưởng huân huy chương cho tập thể
INSERT INTO tbl_reward VALUES(Null,N'Đội sản xuất VLXD',N'Có thành tích xuất sắc trong lao động',1,Null,2,Null,'10-10-2010',N'Nguyễn Minh Triết',N'Chủ tịch nước',1,3,Null,'10-10-2010',Null,Null,5,N'HC-4353',2)
INSERT INTO tbl_reward VALUES(Null,N'Đội nghiên cứu phát triển thị trường',N'Có thành tích xuất sắc',1,Null,2,Null,'11-11-2011',N'Nguyễn Minh Triết',N'Chủ tịch nước',1,2,Null,'11-11-2011',Null,Null,6,'HC-354',2)
INSERT INTO tbl_reward VALUES(Null,N'Đội sản xuất A....',N'Có thành tích xuất sắc trong lao động',1,Null,2,Null,'10-10-2010',N'Nguyễn Minh Triết',N'Chủ tịch nước',1,3,Null,'10-10-2010',Null,Null,5,N'HC-4353',2)
INSERT INTO tbl_reward VALUES(Null,N'Đội nghiên cứu phát triển thị trường',N'Có thành tích xuất sắc',1,Null,2,Null,'11-11-2011',N'Nguyễn Minh Triết',N'Chủ tịch nước',1,2,Null,'11-11-2011',Null,Null,6,'HC-3354',2)
INSERT INTO tbl_reward VALUES(Null,N'Đội sản xuất B....',N'Có thành tích xuất sắc trong lao động',1,Null,2,Null,'10-10-2010',N'Nguyễn Minh Triết',N'Chủ tịch nước',1,3,Null,'10-10-2010',Null,Null,5,N'HC-65653',2)
INSERT INTO tbl_reward VALUES(Null,N'Đội nghiên cứu kỹ thuật',N'Có thành tích xuất sắc',1,Null,2,Null,'11-11-2011',N'Nguyễn Minh Triết',N'Chủ tịch nước',1,2,Null,'11-11-2011',Null,Null,6,'HC-4654',2)

-- Tặng thưởng huân huy chương cho cá nhân
INSERT INTO tbl_reward VALUES(Null,Null,Null,1,Null,2,Null,'10-05-2012',N'Nguyễn Minh Triết',N'Chủ tịch nước',1,2,Null,'11-11-2011',Null,1,Null,'HC-4654',1)
INSERT INTO tbl_reward VALUES(Null,Null,Null,1,Null,2,Null,'01-03-2011',N'Nguyễn Minh Triết',N'Chủ tịch nước',5,3,Null,'11-11-2011',Null,1,Null,'HC-4654',1)
INSERT INTO tbl_reward VALUES(Null,Null,Null,1,Null,2,Null,'09-11-2011',N'Nguyễn Minh Triết',N'Chủ tịch nước',1,1,Null,'11-11-2011',Null,1,Null,'HC-4654',1)
INSERT INTO tbl_reward VALUES(Null,Null,Null,1,Null,2,Null,'10-11-2011',N'Nguyễn Minh Triết',N'Chủ tịch nước',1,2,Null,'11-11-2011',Null,4,Null,'HC-4654',1)
INSERT INTO tbl_reward VALUES(Null,Null,Null,1,Null,2,Null,'08-11-2011',N'Nguyễn Minh Triết',N'Chủ tịch nước',1,2,Null,'11-11-2011',Null,5,Null,'HC-4654',1)

--Ky luat cho ca nhan tap the, cá nhân
INSERT INTO tbl_reward VALUES(N'Phạt cảnh cáo lần 1',Null,N'Không tuân thủ điều lệ ngành',1,Null,3,1,'08-08-2008',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-08-2008',Null,1,Null,346546456546,1)
INSERT INTO tbl_reward VALUES(N'Phạt cảnh cáo lần 1',Null,N'Không tuân thủ điều lệ ngành',1,Null,3,2,'08-08-2008',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'10-08-2008',Null,1,Null,346546456546,1)
INSERT INTO tbl_reward VALUES(N'Phạt cảnh cáo lần 3',Null,N'Đi làm muộn quá số lần quy định',1,Null,3,1,'07-07-2007',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'08-08-2007',Null,2,Null,353453543,1)
INSERT INTO tbl_reward VALUES(N'Phạt A............ ',Null,N'Nghỉ không phép',1,Null,3,2,'05-05-2005',N'Nguyễn Bách Tùng',N'Giám đốc',Null,Null,Null,'06-06-2005',Null,3,Null,53453453,1)
INSERT INTO tbl_reward VALUES(N'Phạt theo điều 2...',N'Nhóm gia công VLXD',N'Nghỉ phép quá quy định',1,Null,3,3,'10-10-2011',N'Xí nghiệp 1',N'Phó giám đốc',Null,Null,Null,'10-10-2011',Null,Null,3,75765757657,2)
INSERT INTO tbl_reward VALUES(N'Phạt theo....',N'Tên Nhóm A',N'Không hoàn thành chỉ tiêu được giao',1,Null,3,3,'02-11-2012',N'Thủ trưởng xí nghiệp 2',N'Trưởng phòng',Null,Null,Null,'03-11-2012',Null,Null,4,3543664564,2)
INSERT INTO tbl_reward VALUES(N'Phạt B....',N'Tên nhóm B',N'Không đưa ra được phương án giải quyết...',1,Null,3,3,'01-10-2012',N'Giám đốc xí nghiệp 1',N'Giám đốc',Null,Null,Null,'02-10-2012',Null,Null,5,6457657657,2)


----------------------------------------------------------------------------------------
Go
create view employee_reward  
AS
SELECT a.employee_id,a.employee_code,a.full_name,a.alias,a.birth_day,a.phone,a.mobile,a.bank_account,a.bank_name,
a.tax_code,a.family_reduction,a.house_land_status,a.email,a.gender,a.ethnic,a.religion,a.hometown,a.current_address,
a.household_registration,a.dateIn_doan,a.dateIn_dang,a.ranks,a.salary_coefficient,a.positions,a.unit,a.date_join,
a.marital_status,a.serial_number_officer,a.serial_number_military,a.identity_card,a.issuing_date,a.place_of_issue,
a.cultural_level,a.army_join,a.employee_status,a.object_employee_id,a.avarta,a.created_date,a.created_by,a.modified_date,
a.modified_by,a.army_identity_card,a.army_issuing_date,a.army_place_of_issue,b.reward_name,b.reward_name_group,b.descriptions,b.reward_status,b.form_reward,b.type_reward,b.skill_reward,
b.signature_date,b.type_medals,b.medal_class,b.Years,b.from_date,b.to_date,b.departments_id,
b.reward_number,b.is_collective,b.signature_name,c.contract_id,c.type_contract_id,c.enable_start,c.enable_end
FROM tbl_employee a left outer join tbl_reward b on a.employee_id=b.employee_id
					left outer join tbl_contract c on a.employee_id=c.employee_id

go

create view employee_ranks_object
as
select de.departments_id,de.departments_name,temp.employee_no,object_employee_name,temp.object_employee_id
from tbl_departments de 
left outer join (
select distinct b.departments_id,count(a.employee_id) as employee_no,f.object_employee_id
from tbl_employee a left outer join tbl_department_employee b on a.employee_id = b.employee_id
					left outer join tbl_object_employee f on a.object_employee_id=f.object_employee_id
group by b.departments_id,f.object_employee_id
having b.departments_id IS NOT NULL) as temp on de.departments_id=temp.departments_id 
left outer join tbl_object_employee oe on temp.object_employee_id=oe.object_employee_id
go

create view employee_reward_individual
as
select a.employee_id,a.employee_code,a.full_name,a.positions,a.ranks,
b.reward_number,b.signature_date,b.descriptions,b.form_reward,b.skill_reward,b.reward_name,b.reward_id 
from tbl_employee a inner join tbl_reward b on a.employee_id =b.employee_id
where b.type_reward=1 and b.is_collective=1
go


create view employee_reward_collective
as
select a.departments_name,a.departments_status,
b.reward_number,b.signature_date,b.descriptions,b.form_reward,b.skill_reward,b.reward_name_group,
b.reward_name,b.departments_id,b.reward_id 
from tbl_reward b left outer join tbl_departments a on a.departments_id =b.departments_id
where b.type_reward=1 and b.is_collective=2
go


--create proc proc_employee_reward_individual_total
--@full_name nvarchar(150)=null,
--@from_date datetime =null ,
--@to_date datetime =null ,
--@skill_reward int=null
--as

--set @from_date= ISNULL(@from_date,DATEADD(mm, -6, getdate()))
--print @from_date
--set @to_date=ISNULL(@to_date,getdate())
--print @to_date

--set @skill_reward=ISNULL(@skill_reward,0)

--if @skill_reward>0  
--begin
--	select temp_all.employee_id,ISNULL(temp_all.total_reward,0) as total_reward,temp_skill.total_reward_skill,temp_skill.skill_reward,
--		   ISNULL(temp_1.total_reward_1,0) as total_reward_1,ISNULL(temp_2.total_reward_2,0) as total_reward_2,
--		   ISNULL(temp_3.total_reward_3,0) as total_reward_3,ISNULL(temp_4.total_reward_4,0) as total_reward_4,
--		   ISNULL(temp_5.total_reward_5,0) as total_reward_5,ISNULL(temp_6.total_reward_6,0) as total_reward_6,
--		   ISNULL(temp_7.total_reward_7,0) as total_reward_7,ISNULL(temp_8.total_reward_8,0) as total_reward_8,
--		   ISNULL(temp_9.total_reward_9,0) as total_reward_9,ISNULL(temp_10.total_reward_10,0) as total_reward_10,
--		   ISNULL(temp_11.total_reward_11,0) as total_reward_11,ISNULL(temp_12.total_reward_12,0) as total_reward_12,
--		   ISNULL(temp_13.total_reward_13,0) as total_reward_13,a.full_name
--	from
--	(select employee_id,count(employee_id) as total_reward from employee_reward_individual where (signature_date>=@from_date and signature_date<=@to_date) group by employee_id) as temp_all left outer join
--	(select employee_id,count(employee_id) as total_reward_skill,skill_reward from employee_reward_individual where (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_skill on temp_all.employee_id=temp_skill.employee_id left outer join
--	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_1,skill_reward from employee_reward_individual where form_reward='1' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_1 on temp_all.employee_id=temp_1.employee_id left outer join
--	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_2,skill_reward from employee_reward_individual where form_reward='2' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_2 on temp_all.employee_id=temp_2.employee_id left outer join
--	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_3,skill_reward from employee_reward_individual where form_reward='3' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_3 on temp_all.employee_id=temp_3.employee_id left outer join
--	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_4,skill_reward from employee_reward_individual where form_reward='4' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_4 on temp_all.employee_id=temp_4.employee_id left outer join
--	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_5,skill_reward from employee_reward_individual where form_reward='5' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_5 on temp_all.employee_id=temp_5.employee_id left outer join
--	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_6,skill_reward from employee_reward_individual where form_reward='6' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_6 on temp_all.employee_id=temp_6.employee_id left outer join
--	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_7,skill_reward from employee_reward_individual where form_reward='7' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_7 on temp_all.employee_id=temp_7.employee_id left outer join
--	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_8,skill_reward from employee_reward_individual where form_reward='8' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_8 on temp_all.employee_id=temp_8.employee_id left outer join
--	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_9,skill_reward from employee_reward_individual where form_reward='9' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_9 on temp_all.employee_id=temp_9.employee_id left outer join
--	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_10,skill_reward from employee_reward_individual where form_reward='10' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_10 on temp_all.employee_id=temp_10.employee_id left outer join
--	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_11,skill_reward from employee_reward_individual where form_reward='11' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_11 on temp_all.employee_id=temp_11.employee_id left outer join
--	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_12,skill_reward from employee_reward_individual where form_reward='12' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_12 on temp_all.employee_id=temp_12.employee_id left outer join
--	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_13,skill_reward from employee_reward_individual where form_reward='13' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_13 on temp_all.employee_id=temp_13.employee_id left outer join
--	tbl_employee a on a.employee_id=temp_all.employee_id
--	where a.full_name like '%'+ ISNULL(@full_name,'')+ '%' and temp_skill.skill_reward = @skill_reward
--end
--else
--begin
--	select temp_all.employee_id,ISNULL(temp_all.total_reward,0) as total_reward,temp_skill.total_reward_skill,temp_skill.skill_reward,
--		   ISNULL(temp_1.total_reward_1,0) as total_reward_1,ISNULL(temp_2.total_reward_2,0) as total_reward_2,
--		   ISNULL(temp_3.total_reward_3,0) as total_reward_3,ISNULL(temp_4.total_reward_4,0) as total_reward_4,
--		   ISNULL(temp_5.total_reward_5,0) as total_reward_5,ISNULL(temp_6.total_reward_6,0) as total_reward_6,
--		   ISNULL(temp_7.total_reward_7,0) as total_reward_7,ISNULL(temp_8.total_reward_8,0) as total_reward_8,
--		   ISNULL(temp_9.total_reward_9,0) as total_reward_9,ISNULL(temp_10.total_reward_10,0) as total_reward_10,
--		   ISNULL(temp_11.total_reward_11,0) as total_reward_11,ISNULL(temp_12.total_reward_12,0) as total_reward_12,
--		   ISNULL(temp_13.total_reward_13,0) as total_reward_13,a.full_name
--	from
--	(select employee_id,count(employee_id) as total_reward from employee_reward_individual where (signature_date>=@from_date and signature_date<=@to_date) group by employee_id) as temp_all left outer join
--	(select employee_id,count(employee_id) as total_reward_skill,skill_reward from employee_reward_individual where (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_skill on temp_all.employee_id=temp_skill.employee_id left outer join
--	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_1,skill_reward from employee_reward_individual where form_reward='1' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_1 on temp_all.employee_id=temp_1.employee_id left outer join
--	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_2,skill_reward from employee_reward_individual where form_reward='2' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_2 on temp_all.employee_id=temp_2.employee_id left outer join
--	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_3,skill_reward from employee_reward_individual where form_reward='3' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_3 on temp_all.employee_id=temp_3.employee_id left outer join
--	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_4,skill_reward from employee_reward_individual where form_reward='4' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_4 on temp_all.employee_id=temp_4.employee_id left outer join
--	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_5,skill_reward from employee_reward_individual where form_reward='5' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_5 on temp_all.employee_id=temp_5.employee_id left outer join
--	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_6,skill_reward from employee_reward_individual where form_reward='6' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_6 on temp_all.employee_id=temp_6.employee_id left outer join
--	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_7,skill_reward from employee_reward_individual where form_reward='7' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_7 on temp_all.employee_id=temp_7.employee_id left outer join
--	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_8,skill_reward from employee_reward_individual where form_reward='8' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_8 on temp_all.employee_id=temp_8.employee_id left outer join
--	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_9,skill_reward from employee_reward_individual where form_reward='9' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_9 on temp_all.employee_id=temp_9.employee_id left outer join
--	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_10,skill_reward from employee_reward_individual where form_reward='10' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_10 on temp_all.employee_id=temp_10.employee_id left outer join
--	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_11,skill_reward from employee_reward_individual where form_reward='11' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_11 on temp_all.employee_id=temp_11.employee_id left outer join
--	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_12,skill_reward from employee_reward_individual where form_reward='12' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_12 on temp_all.employee_id=temp_12.employee_id left outer join
--	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_13,skill_reward from employee_reward_individual where form_reward='13' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_13 on temp_all.employee_id=temp_13.employee_id left outer join
--	tbl_employee a on a.employee_id=temp_all.employee_id
--	where a.full_name like '%'+ ISNULL(@full_name,'')+ '%' and temp_skill.skill_reward in (1,2,3)
--end
--go




--create proc proc_employee_reward_collective_total
--@departments_name nvarchar(150)=null,
--@from_date datetime =null ,
--@to_date datetime =null ,
--@skill_reward int=null
--as

--set @from_date= ISNULL(@from_date,DATEADD(mm, -6, getdate()))
--print @from_date
--set @to_date=ISNULL(@to_date,getdate())
--print @to_date

--set @skill_reward=ISNULL(@skill_reward,0)

--if @skill_reward>0  
--begin
--	select temp_all.departments_id,ISNULL(temp_all.total_reward,0) as total_reward,temp_skill.total_reward_skill,temp_skill.skill_reward,
--		   ISNULL(temp_1.total_reward_1,0) as total_reward_1,ISNULL(temp_2.total_reward_2,0) as total_reward_2,
--		   ISNULL(temp_3.total_reward_3,0) as total_reward_3,ISNULL(temp_4.total_reward_4,0) as total_reward_4,
--		   ISNULL(temp_5.total_reward_5,0) as total_reward_5,ISNULL(temp_6.total_reward_6,0) as total_reward_6,
--		   ISNULL(temp_7.total_reward_7,0) as total_reward_7,ISNULL(temp_8.total_reward_8,0) as total_reward_8,
--		   ISNULL(temp_9.total_reward_9,0) as total_reward_9,ISNULL(temp_10.total_reward_10,0) as total_reward_10,
--		   ISNULL(temp_11.total_reward_11,0) as total_reward_11,ISNULL(temp_12.total_reward_12,0) as total_reward_12,
--		   ISNULL(temp_13.total_reward_13,0) as total_reward_13,a.departments_name
--	from
--	(select departments_id,count(departments_id) as total_reward from employee_reward_collective where (signature_date>=@from_date and signature_date<=@to_date) group by departments_id) as temp_all left outer join
--	(select departments_id,count(departments_id) as total_reward_skill,skill_reward from employee_reward_collective where (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_skill on temp_all.departments_id=temp_skill.departments_id left outer join
--	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_1,skill_reward from employee_reward_collective where form_reward='1' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_1 on temp_all.departments_id=temp_1.departments_id left outer join
--	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_2,skill_reward from employee_reward_collective where form_reward='2' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_2 on temp_all.departments_id=temp_2.departments_id left outer join
--	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_3,skill_reward from employee_reward_collective where form_reward='3' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_3 on temp_all.departments_id=temp_3.departments_id left outer join
--	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_4,skill_reward from employee_reward_collective where form_reward='4' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_4 on temp_all.departments_id=temp_4.departments_id left outer join
--	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_5,skill_reward from employee_reward_collective where form_reward='5' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_5 on temp_all.departments_id=temp_5.departments_id left outer join
--	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_6,skill_reward from employee_reward_collective where form_reward='6' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_6 on temp_all.departments_id=temp_6.departments_id left outer join
--	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_7,skill_reward from employee_reward_collective where form_reward='7' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_7 on temp_all.departments_id=temp_7.departments_id left outer join
--	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_8,skill_reward from employee_reward_collective where form_reward='8' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_8 on temp_all.departments_id=temp_8.departments_id left outer join
--	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_9,skill_reward from employee_reward_collective where form_reward='9' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_9 on temp_all.departments_id=temp_9.departments_id left outer join
--	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_10,skill_reward from employee_reward_collective where form_reward='10' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_10 on temp_all.departments_id=temp_10.departments_id left outer join
--	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_11,skill_reward from employee_reward_collective where form_reward='11' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_11 on temp_all.departments_id=temp_11.departments_id left outer join
--	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_12,skill_reward from employee_reward_collective where form_reward='12' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_12 on temp_all.departments_id=temp_12.departments_id left outer join
--	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_13,skill_reward from employee_reward_collective where form_reward='13' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_13 on temp_all.departments_id=temp_13.departments_id left outer join
--	tbl_departments a on a.departments_id=temp_all.departments_id
--	where a.departments_name like '%'+ ISNULL(@departments_name,'')+ '%' and temp_skill.skill_reward=@skill_reward
--end
--else
--begin
--	select temp_all.departments_id,ISNULL(temp_all.total_reward,0) as total_reward,temp_skill.total_reward_skill,temp_skill.skill_reward,
--		   ISNULL(temp_1.total_reward_1,0) as total_reward_1,ISNULL(temp_2.total_reward_2,0) as total_reward_2,
--		   ISNULL(temp_3.total_reward_3,0) as total_reward_3,ISNULL(temp_4.total_reward_4,0) as total_reward_4,
--		   ISNULL(temp_5.total_reward_5,0) as total_reward_5,ISNULL(temp_6.total_reward_6,0) as total_reward_6,
--		   ISNULL(temp_7.total_reward_7,0) as total_reward_7,ISNULL(temp_8.total_reward_8,0) as total_reward_8,
--		   ISNULL(temp_9.total_reward_9,0) as total_reward_9,ISNULL(temp_10.total_reward_10,0) as total_reward_10,
--		   ISNULL(temp_11.total_reward_11,0) as total_reward_11,ISNULL(temp_12.total_reward_12,0) as total_reward_12,
--		   ISNULL(temp_13.total_reward_13,0) as total_reward_13,a.departments_name
--	from
--	(select departments_id,count(departments_id) as total_reward from employee_reward_collective where (signature_date>=@from_date and signature_date<=@to_date) group by departments_id) as temp_all left outer join
--	(select departments_id,count(departments_id) as total_reward_skill,skill_reward from employee_reward_collective where (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_skill on temp_all.departments_id=temp_skill.departments_id left outer join
--	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_1,skill_reward from employee_reward_collective where form_reward='1' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_1 on temp_all.departments_id=temp_1.departments_id left outer join
--	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_2,skill_reward from employee_reward_collective where form_reward='2' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_2 on temp_all.departments_id=temp_2.departments_id left outer join
--	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_3,skill_reward from employee_reward_collective where form_reward='3' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_3 on temp_all.departments_id=temp_3.departments_id left outer join
--	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_4,skill_reward from employee_reward_collective where form_reward='4' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_4 on temp_all.departments_id=temp_4.departments_id left outer join
--	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_5,skill_reward from employee_reward_collective where form_reward='5' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_5 on temp_all.departments_id=temp_5.departments_id left outer join
--	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_6,skill_reward from employee_reward_collective where form_reward='6' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_6 on temp_all.departments_id=temp_6.departments_id left outer join
--	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_7,skill_reward from employee_reward_collective where form_reward='7' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_7 on temp_all.departments_id=temp_7.departments_id left outer join
--	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_8,skill_reward from employee_reward_collective where form_reward='8' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_8 on temp_all.departments_id=temp_8.departments_id left outer join
--	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_9,skill_reward from employee_reward_collective where form_reward='9' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_9 on temp_all.departments_id=temp_9.departments_id left outer join
--	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_10,skill_reward from employee_reward_collective where form_reward='10' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_10 on temp_all.departments_id=temp_10.departments_id left outer join
--	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_11,skill_reward from employee_reward_collective where form_reward='11' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_11 on temp_all.departments_id=temp_11.departments_id left outer join
--	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_12,skill_reward from employee_reward_collective where form_reward='12' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_12 on temp_all.departments_id=temp_12.departments_id left outer join
--	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_13,skill_reward from employee_reward_collective where form_reward='13' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_13 on temp_all.departments_id=temp_13.departments_id left outer join
--	tbl_departments a on a.departments_id=temp_all.departments_id
--	where a.departments_name like '%'+ ISNULL(@departments_name,'')+ '%' and temp_skill.skill_reward in (1,2,3)
--end
--go


create proc proc_employee_reward_individual_total
@full_name nvarchar(150)=null,
@from_date datetime =null ,
@to_date datetime =null --,
--@skill_reward int=null
as

set @from_date= ISNULL(@from_date,DATEADD(mm, -6, getdate()))
print @from_date
set @to_date=ISNULL(@to_date,getdate())
print @to_date

	select temp_all.employee_id,ISNULL(temp_all.total_reward,0) as total_reward,--temp_skill.total_reward_skill,temp_skill.skill_reward,
		   ISNULL(temp_1.total_reward_1,0) as total_reward_1,ISNULL(temp_2.total_reward_2,0) as total_reward_2,
		   ISNULL(temp_3.total_reward_3,0) as total_reward_3,ISNULL(temp_4.total_reward_4,0) as total_reward_4,
		   ISNULL(temp_5.total_reward_5,0) as total_reward_5,ISNULL(temp_6.total_reward_6,0) as total_reward_6,
		   ISNULL(temp_7.total_reward_7,0) as total_reward_7,ISNULL(temp_8.total_reward_8,0) as total_reward_8,
		   ISNULL(temp_9.total_reward_9,0) as total_reward_9,ISNULL(temp_10.total_reward_10,0) as total_reward_10,
		   ISNULL(temp_11.total_reward_11,0) as total_reward_11,ISNULL(temp_12.total_reward_12,0) as total_reward_12,
		   ISNULL(temp_13.total_reward_13,0) as total_reward_13,ISNULL(temp_14.total_reward_14,0) as total_reward_14,
		   ISNULL(temp_15.total_reward_15,0) as total_reward_15,ISNULL(temp_16.total_reward_16,0) as total_reward_16,
		   a.full_name
	from
	(select employee_id,count(employee_id) as total_reward from employee_reward_individual where (signature_date>=@from_date and signature_date<=@to_date) group by employee_id) as temp_all left outer join
	--(select employee_id,count(employee_id) as total_reward_skill,skill_reward from employee_reward_individual where (signature_date>=@from_date and signature_date<=@to_date) group by employee_id,skill_reward) as temp_skill on temp_all.employee_id=temp_skill.employee_id left outer join
	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_1 from employee_reward_individual where form_reward='1' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id) as temp_1 on temp_all.employee_id=temp_1.employee_id left outer join
	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_2 from employee_reward_individual where form_reward='2' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id) as temp_2 on temp_all.employee_id=temp_2.employee_id left outer join
	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_3 from employee_reward_individual where form_reward='3' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id) as temp_3 on temp_all.employee_id=temp_3.employee_id left outer join
	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_4 from employee_reward_individual where form_reward='4' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id) as temp_4 on temp_all.employee_id=temp_4.employee_id left outer join
	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_5 from employee_reward_individual where form_reward='5' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id) as temp_5 on temp_all.employee_id=temp_5.employee_id left outer join
	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_6 from employee_reward_individual where form_reward='6' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id) as temp_6 on temp_all.employee_id=temp_6.employee_id left outer join
	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_7 from employee_reward_individual where form_reward='7' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id) as temp_7 on temp_all.employee_id=temp_7.employee_id left outer join
	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_8 from employee_reward_individual where form_reward='8' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id) as temp_8 on temp_all.employee_id=temp_8.employee_id left outer join
	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_9 from employee_reward_individual where form_reward='9' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id) as temp_9 on temp_all.employee_id=temp_9.employee_id left outer join
	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_10 from employee_reward_individual where form_reward='10' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id) as temp_10 on temp_all.employee_id=temp_10.employee_id left outer join
	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_11 from employee_reward_individual where form_reward='11' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id) as temp_11 on temp_all.employee_id=temp_11.employee_id left outer join
	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_12 from employee_reward_individual where form_reward='12' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id) as temp_12 on temp_all.employee_id=temp_12.employee_id left outer join
	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_13 from employee_reward_individual where form_reward='13' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id) as temp_13 on temp_all.employee_id=temp_13.employee_id left outer join
	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_14 from employee_reward_individual where form_reward='14' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id) as temp_14 on temp_all.employee_id=temp_14.employee_id left outer join
	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_15 from employee_reward_individual where form_reward='15' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id) as temp_15 on temp_all.employee_id=temp_15.employee_id left outer join
	(select employee_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_16 from employee_reward_individual where form_reward='16' and (signature_date>=@from_date and signature_date<=@to_date) group by employee_id) as temp_16 on temp_all.employee_id=temp_16.employee_id left outer join
	tbl_employee a on a.employee_id=temp_all.employee_id
	where a.full_name like '%'+ ISNULL(@full_name,'')+ '%' --and temp_skill.skill_reward = @skill_reward
go


create proc proc_employee_reward_collective_total
@departments_name nvarchar(150)=null,
@from_date datetime =null ,
@to_date datetime =null --,
--@skill_reward int=null
as

set @from_date= ISNULL(@from_date,DATEADD(mm, -6, getdate()))
print @from_date
set @to_date=ISNULL(@to_date,getdate())
print @to_date


	select temp_all.departments_id,ISNULL(temp_all.total_reward,0) as total_reward ,--temp_skill.total_reward_skill,temp_skill.skill_reward,
		   ISNULL(temp_1.total_reward_1,0) as total_reward_1,ISNULL(temp_2.total_reward_2,0) as total_reward_2,
		   ISNULL(temp_3.total_reward_3,0) as total_reward_3,ISNULL(temp_4.total_reward_4,0) as total_reward_4,
		   ISNULL(temp_5.total_reward_5,0) as total_reward_5,ISNULL(temp_6.total_reward_6,0) as total_reward_6,
		   ISNULL(temp_7.total_reward_7,0) as total_reward_7,ISNULL(temp_8.total_reward_8,0) as total_reward_8,
		   ISNULL(temp_9.total_reward_9,0) as total_reward_9,ISNULL(temp_10.total_reward_10,0) as total_reward_10,
		   ISNULL(temp_11.total_reward_11,0) as total_reward_11,ISNULL(temp_12.total_reward_12,0) as total_reward_12,
		   ISNULL(temp_13.total_reward_13,0) as total_reward_13,ISNULL(temp_14.total_reward_14,0) as total_reward_14,
		   ISNULL(temp_15.total_reward_15,0) as total_reward_15,ISNULL(temp_16.total_reward_16,0) as total_reward_16,
		   a.departments_name
	from
	(select departments_id,count(departments_id) as total_reward from employee_reward_collective where (signature_date>=@from_date and signature_date<=@to_date) group by departments_id) as temp_all left outer join
	--(select departments_id,count(departments_id) as total_reward_skill,skill_reward from employee_reward_collective where (signature_date>=@from_date and signature_date<=@to_date) group by departments_id,skill_reward) as temp_skill on temp_all.departments_id=temp_skill.departments_id left outer join
	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_1 from employee_reward_collective where form_reward='1' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id) as temp_1 on temp_all.departments_id=temp_1.departments_id left outer join
	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_2 from employee_reward_collective where form_reward='2' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id) as temp_2 on temp_all.departments_id=temp_2.departments_id left outer join
	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_3 from employee_reward_collective where form_reward='3' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id) as temp_3 on temp_all.departments_id=temp_3.departments_id left outer join
	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_4 from employee_reward_collective where form_reward='4' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id) as temp_4 on temp_all.departments_id=temp_4.departments_id left outer join
	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_5 from employee_reward_collective where form_reward='5' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id) as temp_5 on temp_all.departments_id=temp_5.departments_id left outer join
	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_6 from employee_reward_collective where form_reward='6' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id) as temp_6 on temp_all.departments_id=temp_6.departments_id left outer join
	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_7 from employee_reward_collective where form_reward='7' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id) as temp_7 on temp_all.departments_id=temp_7.departments_id left outer join
	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_8 from employee_reward_collective where form_reward='8' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id) as temp_8 on temp_all.departments_id=temp_8.departments_id left outer join
	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_9 from employee_reward_collective where form_reward='9' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id) as temp_9 on temp_all.departments_id=temp_9.departments_id left outer join
	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_10 from employee_reward_collective where form_reward='10' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id) as temp_10 on temp_all.departments_id=temp_10.departments_id left outer join
	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_11 from employee_reward_collective where form_reward='11' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id) as temp_11 on temp_all.departments_id=temp_11.departments_id left outer join
	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_12 from employee_reward_collective where form_reward='12' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id) as temp_12 on temp_all.departments_id=temp_12.departments_id left outer join
	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_13 from employee_reward_collective where form_reward='13' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id) as temp_13 on temp_all.departments_id=temp_13.departments_id left outer join
	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_14 from employee_reward_collective where form_reward='14' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id) as temp_14 on temp_all.departments_id=temp_14.departments_id left outer join
	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_15 from employee_reward_collective where form_reward='15' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id) as temp_15 on temp_all.departments_id=temp_15.departments_id left outer join
	(select departments_id,count(CONVERT(INT, ISNULL(form_reward,0))) as total_reward_16 from employee_reward_collective where form_reward='16' and (signature_date>=@from_date and signature_date<=@to_date) group by departments_id) as temp_16 on temp_all.departments_id=temp_16.departments_id left outer join
	tbl_departments a on a.departments_id=temp_all.departments_id
	where a.departments_name like '%'+ ISNULL(@departments_name,'')+ '%' --and temp_skill.skill_reward=@skill_reward
go